
import os
import pymysql
import os
import datetime
import zipfile
import json
from pandas import DataFrame
import hashlib
import gc
import threading
import time
import random
from threading import Thread
import re
from queue import Queue
import  warnings
from loguru import logger
import socket
from common import *
warnings.filterwarnings("ignore")


filename_list_unique = []
class Producer(Thread):
    def __init__(self,filename_list):
        super().__init__()
        self.filename_list = filename_list
        self.father_path = 'E:/project/DataBaseData/NoAnalyze'


    def run(self):
        filename_List = self.filename_list
        while True:
            if len(os.listdir(self.father_path)) == 0:
                logger.info('dir is not enough')
                time.sleep(120)
                continue
            temp = os.listdir(self.father_path)
            for i in temp:
                if '.zip' not in i:
                    continue
                filename_List.put(i)
            time.sleep(180)



class Mysql(Thread):
    def __init__(self,filename_list):
        super().__init__()
        self.filename_list = filename_list
        self.gLock = threading.Lock()
        self.father_path = 'E:/project/DataBaseData/NoAnalyze'


    def data_insert(self,table_name,conn,cursor,filename,name,i,data_list):
        sql = r"""
            create table if not exists data_collection_server.%s(
            id int primary key not null auto_increment comment '编号',
            data_source_id int comment '来源id',
            status varchar(10) comment '状态（成功、失败）',
            title varchar(1000) comment '标题',
            url varchar(255) comment '网址',
            title_date  date comment '网址时间',
            reason  varchar(255) comment '原因（失败的）',
            html_content    varchar(255) comment 'html网站的内容,现存各机子ip',
            read_flag   int comment '是否读取',
            identify   varchar(32) comment '数据唯一标识',
            data_type   int comment '数据类型',
            date_flag   varchar(10) comment '时间标识',
            unique identify(identify),
            index title(title),
            index title_date(title_date),
            index date_flag(date_flag)
            )
        """ % table_name

        try:
            conn.ping(reconnect=True)
            cursor.execute(sql)
            conn.commit()

            logger.info('read success {0}'.format(filename))
            if len(data_list) > 0:
                sql = r'''
                        insert ignore into {0}(data_source_id,status,title,url,title_date,reason,html_content,read_flag,identify,data_type,date_flag)
                        values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
                    '''.format(table_name)
                conn.ping(reconnect=True)
                cursor.executemany(sql, data_list)
                conn.commit()
            return False
        except Exception as e:
            logger.debug('insert wrong , the file is {0} , the reason is {1}'.format(filename,str(e)))
            ls = [[datetime.datetime.now(), e, filename, name[i]]]
            wrong = DataFrame(ls)
            wrong.to_csv('wrong_database.csv', mode='a', index=None, header=None)
            return True


    def identify_query(self,table_name,cursor):
        try:
            sql = 'select identify from {0}'.format(table_name)
            cursor.execute(sql)
            cz_list = [i['identify'] for i in cursor.fetchall()]
        except:
            cz_list = []
        return cz_list


    def run(self):
        filename_list = self.filename_list
        # 连接mysql数据库
        global filename_list_unique
        while True:
            try:
                with Db('data_collection_server') as conn:
                    cursor = conn.cursor()
                    while True:
                        try:
                            if filename_list.qsize() > 0:
                                filename = filename_list.get()
                            else:
                                time.sleep(60)
                                continue
                            if filename in filename_list_unique:
                                continue
                            if 'zip' not in filename:
                                continue

                            self.gLock.acquire()
                            filename_list_unique.append(filename)
                            if len(filename_list_unique) >= 200:
                                filename_list_unique = filename_list_unique[30:]
                            self.gLock.release()
                            try:
                                z = zipfile.ZipFile(os.path.join(self.father_path,filename))
                                z.close()
                            except Exception as e:
                                logger.debug('zip wrong ,the file is {0} , the reason is {1}'.format(filename,str(e)))
                                if filename in filename_list_unique:
                                    filename_list_unique.remove(filename)
                                continue
                            while True:
                                    # 解析读取返回的zip文件
                                    key = False
                                    try:
                                        z = zipfile.ZipFile(os.path.join(self.father_path,filename), 'r')
                                    except Exception as e:
                                        ls = DataFrame([[filename,e,filename]])
                                        ls.to_csv('lll.csv',mode = 'a',header=None,index = None)
                                        break
                                    logger.info('analyze start , the file is {0}'.format(filename))
                                    # 获取该zip下全部文件名
                                    name = z.namelist()
                                    # 逐一处理文件
                                    #一个压缩包下面所有数据
                                    data_list = []
                                    fl_datalist = {}
                                    route_list = []

                                    if len(name) == 1:
                                        cz_list = []
                                    else:
                                        for _ in name:
                                            if '任务' not in _ and '.zip' not in _:
                                                table_name = json.loads(z.read(_).decode('utf-8'))['table_name']
                                                break
                                        cz_list = self.identify_query(table_name, cursor)
                                        table_name = ''

                                    for i in range(len(name)):
                                        if '.zip' in name[i]:
                                            continue
                                        if '/' in name[i]:
                                            continue
                                        if '任务' in name[i]:
                                            #将一个zip的数据批量存入表
                                                # 未建表就建表
                                            if len(data_list) > 0:
                                                data_source_id = data_list[0][0]
                                                sql = 'call id_to_name_handle(%s)' % data_source_id
                                                conn.ping(reconnect=True)
                                                cursor.execute(sql)
                                                table_name = cursor.fetchall()[0]['table_name_use']
                                                key = self.data_insert(table_name, conn, cursor, filename, name, i, data_list)
                                                if key:
                                                    break
                                                data_list = []
                                                logger.info('insert success , the table is {0} , the file is {1}'.format(table_name,filename))
                                            try:
                                                data_mission = json.loads(z.read(name[i]).decode('utf-8'))
                                            except Exception as e:
                                                logger.debug('mission json wrong , the reason is {0}'.format(str(e)))
                                                continue
                                            if 'status' not in data_mission.keys():
                                                continue

                                            mission_status = data_mission['status']
                                            mission_data_source_id = data_mission['data_source_id']
                                            mission_data_type = data_mission['data_type']
                                            mission_reason = data_mission['reason']
                                            mission_start_time = data_mission['star_time']
                                            mission_identify = hashlib.md5((str(mission_data_source_id) + mission_status + mission_reason).encode('utf-8')).hexdigest()
                                            logger.info('missioning')
                                            self.gLock.acquire()
                                            sql = r"""insert into mission_status(data_source_id,status,reason,data_type,identify,start_time)
                                                      values({0},'{1}','{2}',{3},'{4}','{5}')
                                                     """.format(mission_data_source_id,mission_status,mission_reason,mission_data_type,mission_identify,mission_start_time)
                                            try:
                                                conn.ping(reconnect=True)
                                                cursor.execute(sql)
                                                conn.commit()
                                            except Exception as e:
                                                logger.debug('mission insert wrong , the file is {0}-{1} , the reason is {2}'.format(filename,name[i],str(e)))
                                                ls = [[datetime.datetime.now(), e,filename,name[i]]]
                                                wrong = DataFrame(ls)
                                                wrong.to_csv('wrong_database.csv', mode='a',header=None,index =None)

                                            self.gLock.release()
                                            logger.info('mission status insert success !!')
                                        else:
                                            #解析json
                                            try:
                                                data = json.loads(z.read(name[i]).decode('utf-8'))
                                            except Exception as e:
                                                logger.debug('json wrong , the file is {0}-{1} , the reason is {2}'.format(filename,name[i],str(e)))
                                                ls = [[datetime.datetime.now(), e,filename,name[i]]]
                                                wrong = DataFrame(ls)
                                                wrong.to_csv('wrong_database.csv', mode='a',header=None,index =None)
                                                continue
                                            #获取各字段的值
                                            table_name = data['table_name']
                                            data_source_id = data['data_source_id']
                                            status = data['status']
                                            title = data['title']
                                            url = data['url']
                                            title_date = data['titleDate']
                                            if len(title_date) != 10 and title_date != '':
                                                try:
                                                    title_date = datetime.datetime.strptime(title_date, "%Y-%m-%d").strftime("%Y-%m-%d")
                                                except Exception as e:
                                                    logger.debug('date wrong , the file is {0},the reason is {1}'.format(filename,str(e)))
                                            if title_date != '':
                                                temp_title_date = title_date.split('-')
                                                if len(temp_title_date) != 3:
                                                    logger.debug('date wrong ,the id is {0},the date is {1}'.format(data_source_id,title_date))
                                                    ls = [[filename,title_date,name[i],datetime.datetime.now(),name[i]]]
                                                    tt = DataFrame(ls)
                                                    tt.to_csv('date_wrong.csv',mode = 'a',index = None,header=None)
                                                    continue
                                                else:
                                                    if (temp_title_date[1] > '12') or (temp_title_date[2] > '31'):
                                                        logger.debug('date wrong ,the id is {0},the date is {1}'.format(data_source_id,title_date))
                                                        ls = [[filename,title_date,name[i],datetime.datetime.now(),name[i]]]
                                                        tt = DataFrame(ls)
                                                        tt.to_csv('date_wrong.csv',mode = 'a',index = None,header=None)
                                                        continue
                                            reason = data['reason']
                                            # 获取计算机名称
                                            hostname = socket.gethostname()
                                            # 获取本机IP
                                            ip = socket.gethostbyname(hostname)
                                            html_content = ip
                                            title = title.replace("'",'"')

                                            url = url.replace("'", '"')
                                            if '.pdf' in url:
                                                continue
                                            data_type = data['data_type']
                                            read_flag = 0
                                            # 将title、url、title_date封装为一个标识，md5加密
                                            identify = hashlib.md5((title + url + title_date).encode('utf-8')).hexdigest()
                                            if identify in cz_list:
                                                continue
                                            # 将数据存入数据库
                                            # 将失败状态单独存入一张表
                                            # 将成功状态存入数据库
                                            if (status == '成功') and url != '' and title != '' and title_date != '':
                                                data_list.append((data_source_id, status, title, url, title_date, reason,html_content, read_flag, identify, data_type,datetime.datetime.strftime(datetime.datetime.now(),'%Y-%m-%d')))

                                                fl_datalist[title_date] = []
                                                fl_datalist[title_date].append({'titleDate':data['titleDate'],'data_source_id':data_source_id,'zip_name':filename,'json_name':name[i],'text':bytes.decode(z.read(name[i])),'type':data['data_type']})
                                                route_list.append(self.pack(fl_datalist))
                                                fl_datalist = {}

                                            #将一个zip的数据批量存入表
                                            if (i == (len(name) - 2)) or (len(data_list) >= 30) :
                                                # 未建表就建表
                                                if len(data_list) > 0:
                                                    key = self.data_insert(table_name, conn, cursor, filename, name, i, data_list)
                                                    if key:
                                                        break
                                                    data_list = []
                                                    logger.info('insert success , the table is {0} , the filename is {1}'.format(table_name,filename))

                                    z.close()
                                    route_datalist = {}
                                    if len(route_list) > 0:
                                        for i in route_list:
                                            if i['zip_name'] not in route_datalist:
                                                route_datalist[i['zip_name']] = []
                                            route_datalist[i['zip_name']].append({'json_name': i['json_name'], 'route': i['route']})
                                        key = self.compress(route_datalist)
                                    if key:
                                        break
                                    os.remove(os.path.join(self.father_path,filename))

                                    logger.info('rename success ,the file is {0}'.format(filename))
                                    break
                        except Exception as e:
                            logger.debug('all wrong , the reson is {0}'.format(str(e)))
                            ls = [[datetime.datetime.now(),e,filename,'all']]
                            wrong = DataFrame(ls)
                            wrong.to_csv('wrong_database.csv',mode = 'a',index = None,header=None)
                            time.sleep(10)
            except Exception as e:
                logger.debug('all rec wrong , the reason is '.format(str(e)))
                continue

    def pack(self,fl_datalist):
        lujin = self.father_path
        json_name = {}
        for i in fl_datalist:
            for n in fl_datalist[i]:
                if n['type'] not in (1, 2, 3, 4, 5):
                    n['type'] = 0
                data_type = str(n['type'])
                folder = lujin + '\\' + data_type + '\\' +str(n['data_source_id'])+'\\'+ i
                if not os.path.exists(folder):
                    os.makedirs(folder)
                with open(folder + '\\' + n['json_name'], 'w') as fh:
                    fh.write(n['text'])
                json_name = {'json_name':n['json_name'],'route':folder + '\\' + n['json_name'],'zip_name':folder + '\\' + n['zip_name']}
        return json_name

    def compress(self,fl_datalist):
        try:
            for i in fl_datalist:
                zip = zipfile.ZipFile(i, 'w', zipfile.ZIP_DEFLATED)
                for n in fl_datalist[i]:
                    zip.write(n['route'], n['json_name'])
                    os.remove(n['route'])
                zip.close()
            return False
        except Exception as e:
            logger.debug('compress wrong ,the reason is {0}'.format(str(e)))
            return True


def main():
    filename_list = Queue(10000)
    for i in range(1):
        t = Producer(filename_list)
        t.start()
    for i in range(1):
        t = Mysql(filename_list)
        t.start()


main()

